package lab03.test13;


import java.sql.*;

public class Test13 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jc.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/java_2017?serverTimezone=UTC",
                    "root",
                    "123456"
            );

            statement = connection.createStatement();
            String sql = "insert into t_students(stu_no,real_name,age, gender,department) value('100005','xiao',12,1,'ds')";
            statement.execute(sql);
            String sql2 = "insert into t_students(stu_no,real_name,age, gender,department) value('100006','li',13,0,'ds')";
            statement.execute(sql2);
            String sql3 = "insert into t_students(stu_no,real_name,age, gender,department) value('100007','王',14,0,'cs')";
            statement.execute(sql3);
            String sql4 = "insert into t_students(stu_no,real_name,age, gender,department) value('100008','xi',15,1,'ds')";
            statement.execute(sql4);
            String sql5 = "insert into t_students(stu_no,real_name,age, gender,department) value('100009','ao',11,1,'ds')";
            statement.execute(sql5);
            String sql6 = "insert into t_students(stu_no,real_name,age, gender,department) value('100001','xia',13,1,'cs')";
            statement.execute(sql6);
            String sql7 = "insert into t_students(stu_no,real_name,age, gender,department) value('100002','xiang',12,1,'ds')";
            statement.execute(sql7);
            String sql8 = "insert into t_students(stu_no,real_name,age, gender,department) value('100003','ming',14,0,'cs')";
            statement.execute(sql8);
            String sql9 = "insert into t_students(stu_no,real_name,age, gender,department) value('100004','fang',12,0,'ds')";
            statement.execute(sql9);
            System.out.println("遍历:");
            resultSet = statement.executeQuery("SELECT * FROM t_students");
            while(resultSet.next()){
                System.out.print(resultSet.getInt(1)+" \t");
                System.out.print(resultSet.getString(2)+" \t");
                System.out.print(resultSet.getString(3)+" \t");
                System.out.print(resultSet.getInt(4)+" \t");
                System.out.print(resultSet.getInt(5)+" \t");
                System.out.print(resultSet.getString(6));
                System.out.println();
            }

            statement.execute("DELETE FROM t_students WHERE id = 5");
            resultSet = statement.executeQuery("SELECT * FROM t_students");
            System.out.println("after delete:");
            while(resultSet.next()){
                System.out.print(resultSet.getInt(1)+" \t");
                System.out.print(resultSet.getString(2)+" \t");
                System.out.print(resultSet.getString(3)+" \t");
                System.out.print(resultSet.getInt(4)+" \t");
                System.out.print(resultSet.getInt(5)+" \t");
                System.out.print(resultSet.getString(6));
                System.out.println();
            }
            statement.execute("UPDATE t_students SET department = '信息与工程学院' WHERE id = 3");
            resultSet = statement.executeQuery("SELECT * FROM t_students");
            System.out.println("after update:");
            while(resultSet.next()){
                System.out.print(resultSet.getInt(1)+" \t");
                System.out.print(resultSet.getString(2)+" \t");
                System.out.print(resultSet.getString(3)+" \t");
                System.out.print(resultSet.getInt(4)+" \t");
                System.out.print(resultSet.getInt(5)+" \t");
                System.out.print(resultSet.getString(6));
                System.out.println();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
